package com.yusheng.dao;

import com.yusheng.entity.ApprovalRecord;
import com.yusheng.entity.ApprovalRecordList;
import com.yusheng.util.JdbcUtil;
import sun.rmi.runtime.NewThreadAction;

import java.sql.ResultSet;
import java.sql.SQLException;

public class QueryAppproval {
    public static JdbcUtil jdbcUtil=new JdbcUtil();

    public static void main(String[] args) {
//        ApprovalRecordList approvalRecordList=getApprovalsByApprovalNo("120002");
//        boolean b=isTurnMe("1500018","1");
        ApprovalRecordList approvalRecordList=getApprovalsByRecordNo("1500017");
        System.out.println(approvalRecordList);
    }

    /**
     * 测试是不是轮到我审批了，就是判断自己上一步审批的  finished   如果没有上一步，那么我是第一步，就轮到我审批 isTurn=true
     *          如果上一步  finished=0;就代表上一步未审批，就没有轮到我 ，isTurn=false
     *          如果上一步  finished=1;就代表被驳回了，我也可以看到 ，isTurn=true
     *          如果上一步  finished=2;就代表上一步审批通过了，轮到我了 ，isTurn=true
     *
     * @param mainRecord   主审批编码
     * @param cuurentRecord  当前是第几步
     * @return
     */
    public static boolean isTurnMe(String mainRecord,String cuurentRecord) {
        boolean isTurn=false;
        try {
            jdbcUtil.getConnection1();
            int current=Integer.valueOf(cuurentRecord);
            current--;
            String sql="SELECT finished\n" +
                    "from approvalrecords\n" +
                    "where mainrecord=? and currentrecord=?";
            jdbcUtil.preparedStatement=jdbcUtil.connection1.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,mainRecord);
            jdbcUtil.preparedStatement.setString(2, String.valueOf(current));
            ResultSet resultSet=jdbcUtil.preparedStatement.executeQuery();
            if (!resultSet.next()) {
                isTurn=true;
            }else {
               String s= resultSet.getString(1);
               if ("1".equals(s)||"2".equals(s)){
                   isTurn=true;
               }
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtil.close1();
        }
        return isTurn;

    }

    /**
     * 通过申请人号获取申请记录
     * @param stuffNo  申请人的编号
     * @return
     */
    public static ApprovalRecordList getApprovalsByApprovalNo(String stuffNo) {
        ApprovalRecordList approvalRecordList = new ApprovalRecordList();
        try {
            jdbcUtil.getConnection();

            String sql = "select a.recordno,a.staffno,b.staffname,a.approvalno,c.staffname,a.mainrecord,a.currentrecord,a.finished,a.time, a.recordname,a.recordopinion,a.recordopinion1\n" +
                    "from approvalrecords a\n" +
                    "join staff b\n" +
                    "on  a.staffno=b.staffno\n" +
                    "join staff c\n" +
                    "on a.approvalno=c.staffno\n" +
                    "where a.approvalno=?\n" +
                    "ORDER BY a.time desc";
            jdbcUtil.preparedStatement = jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1, stuffNo);
            ResultSet resultSet = jdbcUtil.preparedStatement.executeQuery();
            while (resultSet.next()) {
                boolean isTurn=isTurnMe(resultSet.getString(6),resultSet.getString(7));
                if (!isTurn) continue;
                String finished = "未审批";
                if ("1".equals(resultSet.getString(8))) finished = "审批没通过";
                else if ("2".equals(resultSet.getString(8))) finished = "审批通过";
                ApprovalRecord approvalRecord = new ApprovalRecord(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        finished,
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12)
                );
                approvalRecordList.approvalRecordList.add(approvalRecord);
                approvalRecordList.num++;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return approvalRecordList;
    }

    /**
     * 根据记录号获取自己以及自己前面的审批流程
     * @param recordNo
     * @return
     */
    public static ApprovalRecordList getApprovalsByRecordNo(String recordNo) {
        ApprovalRecordList approvalRecordList = new ApprovalRecordList();
        try {
            jdbcUtil.getConnection();

            String sql = "select a.recordno,a.staffno,b.staffname,a.approvalno,c.staffname,a.mainrecord,a.currentrecord,a.finished,a.time, a.recordname,a.recordopinion,a.recordopinion1\n" +
                    "from approvalrecords a\n" +
                    "join staff b\n" +
                    "on  a.staffno=b.staffno\n" +
                    "join staff c\n" +
                    "on a.approvalno=c.staffno\n" +
                    "where a.mainrecord=(select mainrecord from  approvalrecords where recordno=?)\n" +
                    "and   a.currentrecord<=(select currentrecord from approvalrecords where recordno=?)";
            jdbcUtil.preparedStatement = jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1, recordNo);
            jdbcUtil.preparedStatement.setString(2, recordNo);
            ResultSet resultSet = jdbcUtil.preparedStatement.executeQuery();
            while (resultSet.next()) {
                String finished = "未审批";
                if ("1".equals(resultSet.getString(8))) finished = "审批没通过";
                else if ("2".equals(resultSet.getString(8))) finished = "审批通过";
                ApprovalRecord approvalRecord = new ApprovalRecord(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        finished,
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12)
                );
                approvalRecordList.approvalRecordList.add(approvalRecord);
                approvalRecordList.num++;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.close();
        }
        return approvalRecordList;
    }

    public static boolean approvalNot(String mainRecord, String opionion) {
        boolean success=false;
        String sql="UPDATE approvalrecords\n" +
                "set  finished='1' ,recordopinion1=?\n" +
                "where mainrecord=?";
        try {
            jdbcUtil.getConnection();
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,opionion);
            jdbcUtil.preparedStatement.setString(2,mainRecord);
            int count=jdbcUtil.preparedStatement.executeUpdate();
            if (count>=1) success=true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return success;
    }

    public static boolean approvalYes(String recordNo, String opionion) {
        boolean success=false;
        String sql="UPDATE approvalrecords\n" +
                "set  finished='2' ,recordopinion1=?\n" +
                "where recordno=?";
        try {
            jdbcUtil.getConnection();
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,opionion);
            jdbcUtil.preparedStatement.setString(2,recordNo);
            int count=jdbcUtil.preparedStatement.executeUpdate();
            if (count>=1) success=true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return success;
    }
}
